const mysql = require('mysql2/promise');

const dbConfig = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs'
};

async function migrateSystemMessagesTable() {
    let connection;

    try {
        console.log('🔌 连接到数据库...');
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ 数据库连接成功');

        // 1. 备份现有数据
        console.log('📋 备份现有 system_messages 表数据...');
        const [existingData] = await connection.execute(
            'SELECT * FROM system_messages ORDER BY id'
        );
        console.log(`✅ 备份了 ${existingData.length} 条现有数据`);

        // 2. 删除旧表
        console.log('🗑️ 删除旧的 system_messages 表...');
        await connection.execute('DROP TABLE IF EXISTS system_messages');
        console.log('✅ 旧表已删除');

        // 3. 创建新表结构
        console.log('📋 创建新的 system_messages 表...');
        const createTableSQL = `
            CREATE TABLE \`system_messages\` (
              \`id\` bigint NOT NULL AUTO_INCREMENT COMMENT '消息ID',
              \`senderId\` int NOT NULL DEFAULT '0' COMMENT '发送者ID，0表示系统',
              \`senderName\` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '系统' COMMENT '发送者名称',
              \`messageBody\` json NOT NULL COMMENT '消息体，存储完整的消息数据格式',
              \`messageType\` tinyint NOT NULL COMMENT '消息类型：1-个人消息，2-广播消息',
              \`receiverId\` int DEFAULT NULL COMMENT '接收者ID，广播消息可为空',
              \`isRead\` tinyint NOT NULL DEFAULT '0' COMMENT '是否已读：0-未读，1-已读',
              \`createdAt\` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
              \`updatedAt\` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
              PRIMARY KEY (\`id\`),
              KEY \`idx_sender_created\` (\`senderId\`,\`createdAt\`),
              KEY \`idx_receiver_created\` (\`receiverId\`,\`createdAt\`),
              KEY \`idx_message_type_created\` (\`messageType\`,\`createdAt\`),
              KEY \`idx_is_read_created\` (\`isRead\`,\`createdAt\`),
              KEY \`idx_created_at\` (\`createdAt\`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统消息表'
        `;

        await connection.execute(createTableSQL);
        console.log('✅ 新表创建成功');

        // 4. 迁移现有数据
        console.log('📝 迁移现有数据到新表结构...');
        let migratedCount = 0;
        let skippedCount = 0;

        for (const oldRecord of existingData) {
            try {
                // 根据旧数据构建新的消息体格式
                let messageBody;
                let messageType;
                let receiverId = null;
                let isRead = 1; // 广播消息默认已读

                // 判断消息类型：如果有 fullMessageData，使用它；否则构建广播消息格式
                if (oldRecord.fullMessageData) {
                    messageBody = oldRecord.fullMessageData;
                    messageType = 2; // 广播消息
                } else {
                    // 构建广播消息格式
                    messageBody = {
                        act_id: oldRecord.actId || 260,
                        code: oldRecord.code || 0,
                        data: {
                            c: oldRecord.content || '',
                            h: oldRecord.messageTime || '',
                            id: oldRecord.messageId || oldRecord.id,
                            n: oldRecord.senderName || '系统',
                            rid: oldRecord.senderId || 0,
                            t: oldRecord.typeFlag || 6
                        }
                    };
                    messageType = 2; // 广播消息
                }

                // 插入新格式的数据
                await connection.execute(
                    `INSERT INTO system_messages (
                        senderId, senderName, messageBody, messageType, 
                        receiverId, isRead, createdAt, updatedAt
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
                    [
                        oldRecord.senderId || 0,
                        oldRecord.senderName || '系统',
                        JSON.stringify(messageBody),
                        messageType,
                        receiverId,
                        isRead,
                        oldRecord.createdAt || new Date(),
                        oldRecord.updatedAt || new Date()
                    ]
                );

                migratedCount++;
            } catch (error) {
                console.warn(`⚠️ 跳过记录 ID ${oldRecord.id}: ${error.message}`);
                skippedCount++;
            }
        }

        console.log(`✅ 数据迁移完成: 成功迁移 ${migratedCount} 条，跳过 ${skippedCount} 条`);

        // 5. 验证迁移结果
        console.log('🔍 验证迁移结果...');
        const [newData] = await connection.execute('SELECT COUNT(*) as count FROM system_messages');
        console.log(`✅ 新表中共有 ${newData[0].count} 条记录`);

        // 6. 插入示例数据
        console.log('📝 插入示例数据...');
        const sampleBroadcastMessage = {
            act_id: 260,
            code: 0,
            data: {
                c: '本次游乐场乾坤八卦开1,2,3大双,共奖出1000金500银两!',
                h: '15:30',
                id: Date.now(),
                n: '系统',
                rid: 1000,
                t: 6
            }
        };

        const samplePersonalMessage = {
            act_id: 10,
            info_id: 1000,
            state: 1,
            receiverCharacterId: 123,
            messageContent: '恭喜,您在大小玩法中中奖，获得100金币！'
        };

        await connection.execute(
            `INSERT INTO system_messages (
                senderId, senderName, messageBody, messageType, 
                receiverId, isRead, createdAt, updatedAt
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
            [
                1000, '系统', JSON.stringify(sampleBroadcastMessage), 2,
                null, 1, new Date(), new Date()
            ]
        );

        await connection.execute(
            `INSERT INTO system_messages (
                senderId, senderName, messageBody, messageType, 
                receiverId, isRead, createdAt, updatedAt
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
            [
                1000, '系统', JSON.stringify(samplePersonalMessage), 1,
                123, 0, new Date(), new Date()
            ]
        );

        console.log('✅ 示例数据插入成功');

        console.log('🎉 system_messages 表迁移完成！');
        console.log('');
        console.log('📋 新表结构说明:');
        console.log('- senderId: 发送者ID，0表示系统');
        console.log('- senderName: 发送者名称');
        console.log('- messageBody: 消息体，存储完整的消息数据格式');
        console.log('- messageType: 消息类型，1-个人消息，2-广播消息');
        console.log('- receiverId: 接收者ID，广播消息可为空');
        console.log('- isRead: 是否已读，0-未读，1-已读');
        console.log('- createdAt/updatedAt: 创建和更新时间');

    } catch (error) {
        console.error('❌ 迁移失败:', error);
        throw error;
    } finally {
        if (connection) {
            await connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }
}

// 运行迁移
if (require.main === module) {
    migrateSystemMessagesTable()
        .then(() => {
            console.log('✅ 迁移脚本执行完成');
            process.exit(0);
        })
        .catch((error) => {
            console.error('❌ 迁移脚本执行失败:', error);
            process.exit(1);
        });
}

module.exports = { migrateSystemMessagesTable };
